import java.sql.*;

public class CRUDTest {
    static Connection conn = null;
    static Statement st = null;
    static PreparedStatement ps = null;
    static ResultSet rs= null;
    public static void main(String[] args) {
        listAll();
        //add();
        //update();
        //delete();
        //getUserByNum();
    }
    public static void listAll() {
        try {
            conn = DBConnection.getConnection();
            String sql = "select id,name,age from testDB.student";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                System.out.println("id="+id+";name="+name+";age="+age);
            }
            System.out.println("------------------------------");
            DBConnection.close(rs,st,conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //插入
    public static void add() {
        try {
            conn = DBConnection.getConnection();
            String sql = "insert into testDB.student (name,age) values (?,?);";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"樊语响");
            ps.setInt(2,20);
            ps.executeUpdate();
            DBConnection.close(rs,ps,conn);
            listAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //更新
    public static void update() {
        try {
            conn = DBConnection.getConnection();
            String sql = "update testDB.student set name = ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"崔佳宸");
            ps.setInt(2,1);
            ps.executeUpdate();
            DBConnection.close(rs,ps,conn);
            listAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //删除
    public static void delete() {
        try {
            conn = DBConnection.getConnection();
            String sql = "delete from testDB.student where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,1);
            ps.executeUpdate();
            DBConnection.close(rs,ps,conn);
            listAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}